from SystematicFactors.General import *
import os
import Core.Gadget as Gadget
import SystematicFactors.DoomsdayClock as DoomsdayClock

#
def Calc_UpDown(database, datetime1, datetime2):
    #
    def Calc_UpDown_Sub(database, index_symbol, datetime1, datetime2):
        #
        # fields=["tech_uppct", "tech_downpct", "tech_limituppct", "tech_limitdownpct"]
        # 部分指数，如wind全A没有比例字段，故用数量除以总数，得到比例
        df = WSD_Multi_Fields(index_symbol, datetime1, datetime2,
                              fields=["tech_upnum", "tech_downnum", "tech_limitupnum", "tech_limitdownnum",
                                      "numberofconstituents2"])
        #
        df["tech_uppct"] = df["tech_upnum"] / df["numberofconstituents2"]
        df["tech_downpct"] = df["tech_downnum"] / df["numberofconstituents2"]
        df["tech_limituppct"] = df["tech_limitupnum"] / df["numberofconstituents2"]
        df["tech_limitdownpct"] = df["tech_limitdownnum"] / df["numberofconstituents2"]
        # print(df)

        df_daily = df.copy()
        df_daily["UpDown_Gap"] = df_daily["tech_uppct"] - df_daily["tech_downpct"]
        df_daily["UpDownLimit_Gap"] = df_daily["tech_limituppct"] - df_daily["tech_limitdownpct"]
        df_daily["Report_Date"] = df_daily.index
        df_daily["Release_Date"] = df_daily.index
        # print(df_daily)
        # df_daily.to_csv("d://Calc_UpDown1.csv")

        # 平滑数据
        df_smooth = df.rolling(window=5).mean()
        #
        df_smooth["Report_Date"] = df_smooth.index
        df_smooth["Release_Date"] = df_smooth.index
        df_smooth["UpDown_Gap"] = df_smooth["tech_uppct"] - df_smooth["tech_downpct"]
        df_smooth["UpDownLimit_Gap"] = df_smooth["tech_limituppct"] - df_smooth["tech_limitdownpct"]
        print(df_smooth)
        # df_smooth.to_csv("d://Calc_UpDown2.csv")

        df_smooth.dropna(subset=["UpDown_Gap"], inplace=True)

        # 变频
        df_smooth.index = pd.to_datetime(df_smooth.index)
        df_weekly = df_smooth.resample('W').last()
        df_weekly["Report_Date"] = df_weekly.index
        #
        print(df_weekly[["Release_Date","UpDown_Gap","UpDownLimit_Gap"]])
        #
        df_weekly.dropna(subset=["UpDown_Gap"], inplace=True)
        #
        symbol_name = ""
        if index_symbol == "000300.SH":
            symbol_name = "_HS300"
        elif index_symbol == "881001.WI":
            symbol_name = ""
        else:
            print("Unrecognized Index", index_symbol)
            return
        #
        Save_Systematic_Factor_To_Database(database, df_daily, save_name="UpDown_Ratio_Gap" + symbol_name, field_name="UpDown_Gap")
        Save_Systematic_Factor_To_Database(database, df_smooth, save_name="UpDown_Ratio_Gap_Smooth" + symbol_name, field_name="UpDown_Gap")
        Save_Systematic_Factor_To_Database(database, df_weekly, save_name="UpDown_Ratio_Gap_Smooth" + symbol_name + "_Weekly",
                                                                field_name="UpDown_Gap")
        #
        Save_Systematic_Factor_To_Database(database, df_daily, save_name="UpDownLimit_Ratio_Gap" + symbol_name,
                                                                field_name="UpDownLimit_Gap")
        #
        Save_Systematic_Factor_To_Database(database, df_smooth, save_name="UpDownLimit_Ratio_Gap_Smooth" + symbol_name,
                                                                field_name="UpDownLimit_Gap")
        #
        Save_Systematic_Factor_To_Database(database, df_weekly,
                                           save_name="UpDownLimit_Ratio_Gap_Smooth" + symbol_name + "_Weekly",
                                           field_name="UpDownLimit_Gap")

    #
    Calc_UpDown_Sub(database, "000300.SH", datetime1, datetime2)
    Calc_UpDown_Sub(database, "881001.WI", datetime1, datetime2)  # Wind全A
    pass


# 该计算方式存在问题：
# 基差未能年化处理，
# 未进行加权，
# IF.CFE会自动切换主力合约，产生价格跳变
def Calc_Basis(database, datetime1, datetime2):
    df = WSD_Quote(['000300.SH', 'IF.CFE'], datetime1, datetime2)
    # print(df)
    df["Release_Date"] = df.index

    df.fillna(method='ffill', inplace=True)
    df['Basis'] = df['000300.SH'] / df['IF.CFE'] - 1

    df.index = pd.to_datetime(df.index)
    df_weekly = df.resample('W').last()

    # 【2020-01-20 王新博
    # 根据对比文件，增加了取相反数的处理】
    df_weekly["Basis"] = -df_weekly["Basis"]
    df_weekly["Report_Date"] = df_weekly.index
    print(df_weekly)

    #
    # df_weekly.dropna(subset=["Basis"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name="IF_Basis", field_name="Basis")



def Calc_BlockTrade(database, datetime1, datetime2):
    #
    filter = {}
    filter["Date"] = {">=": datetime1, "<=": datetime2}
    documents = database.Find("financial_data", "stock_block_trade", filter)
    df = Gadget.DocumentsToDataFrame(documents)
    df.rename(columns={"date": "Date", "amount": "Amount", "rate_discount": "Rate_Discount"}, inplace=True)
    #
    data = []
    dfGroups = df.groupby("Date")
    for dfGroup in dfGroups:
        date = dfGroup[0]
        dfTemp = dfGroup[1].copy()
        dfTemp["Weight"] = 0
        dfTemp["Weighted_Discount"] = 0
        #
        sum = dfTemp["Amount"].sum()
        dfTemp["Weight"] = dfTemp["Amount"] / sum
        dfTemp["Weighted_Discount"] = dfTemp["Rate_Discount"] * dfTemp["Weight"]
        weightedDiscount = dfTemp["Weighted_Discount"].sum()
        #
        data.append([date, sum, weightedDiscount])
    #
    dfDaily = pd.DataFrame(data, columns=["Date", "Amount", "Rate_Discount"])
    # dfDaily.set_index(['Date'], inplace=True)
    #
    Save_Systematic_Factor_To_Database(database, dfDaily, save_name="BlockTrade_Amt", field_name="Amount")
    Save_Systematic_Factor_To_Database(database, dfDaily, save_name="BlockTrade_Discount", field_name="Rate_Discount")
    #
    print(dfDaily)
    print("Inserted BlockTrade-Daily from", datetime1, "to", datetime2, "#", len(dfDaily))


# 融资交易额 / 总交易额，占比
def Calc_MarginTrade_FinAmt_2_TotalAmt(database, df, datetime1, datetime2):
    #
    df["Total_Amount"] = (df["TotalAmt_SH"] + df["TotalAmt_SZ"]) * 10000 # 单位亿，统一到万
    df["Finance_Amount"] = df["FinancingBuy_SH"] + df["FinancingSel_SH"] + df["FinancingBuy_SZ"] + df["FinancingSel_SZ"]
    df["Finance_Amount"] = df["Finance_Amount"]
    df["FinAmt_To_TotalAmt"] = df["Finance_Amount"] * 0.5 / df["Total_Amount"]
    #
    print("融资余额 / 流通市值比 ver 1")
    # print(df)
    print(df[["Finance_Amount", "Total_Amount", "FinAmt_To_TotalAmt"]])
    # print(df.iloc[-1])
    Save_Systematic_Factor_To_Database(database, df, 'FinAmt_To_TotalAmt')  # 融资交易额，总交易额，占比


# --- 融资余额市值占比---
def Calc_MarginTrade_FinBalance_2_Cap(database, df, datetime1, datetime2):

    # 融资余额占流通市值比
    df["Total_Cap"] = df["CirculateCap_SH"] + df["CirculateCap_SZ"]
    df["FinBalance_To_Cap"] = df["MarginTrade_FinBalance"] / df["Total_Cap"]
    #
    print("融资余额 / 流通市值比 ver 1")
    # print(df)
    # print(df.iloc[-1])
    print(df[["MarginTrade_FinBalance", "Total_Cap", "FinBalance_To_Cap"]])
    Save_Systematic_Factor_To_Database(database, df, 'FinBalance_To_Cap')  #


# 融资交易额 / 总交易额，占比
def Calc_MarginTrade_FinAmt_2_TotalAmt_v2(database, df, datetime1, datetime2):
    # Ver2
    df["Amount"] = df["TotalAmt_SZ"] + df["TotalAmt_SH"]
    #
    df['Fin_Avg5'] = df['M0075987'].rolling(window=5, min_periods=1).mean()
    df['Amount_Avg5'] = df['Amount'].rolling(window=5, min_periods=1).mean()
    #
    df["Fin2Amount_Avg5"] = df['Fin_Avg5'] / df["Amount_Avg5"]
    # df.to_csv("d:/My_finAmount.csv")

    df_fin2amount_2_weekly = df.resample('W').last()
    df_fin2amount_2_weekly["Report_Date"] = df_fin2amount_2_weekly.index
    df_fin2amount_2_weekly['FinAmt_To_TotalAmt_v2_Weekly_Dif'] = df_fin2amount_2_weekly['Fin2Amount_Avg5'].diff()
    #
    print("融资余额 / 流通市值比 ver 2")
    print(df_fin2amount_2_weekly[["Fin2Amount_Avg5", "FinAmt_To_TotalAmt_v2_Weekly_Dif"]])
    Save_Systematic_Factor_To_Database(database, df_fin2amount_2_weekly, 'FinAmt_To_TotalAmt_v2', field_name="Fin2Amount_Avg5")  #
    Save_Systematic_Factor_To_Database(database, df_fin2amount_2_weekly, 'FinAmt_To_TotalAmt_v2_Weekly_Dif')  #


def Calc_MarginTrade_FinBalance_2_Cap_v2(database, df, datetime1, datetime2):
    # Ver2
    df["Total_Cap"] = df["CirculateCap_SH"] + df["CirculateCap_SZ"]
    df_weekly = df.resample('W').agg({"MarginTrade_FinBalance": "last", "Total_Cap": "mean", "Release_Date": "last"})  # 周平均市值
    df_weekly["Report_Date"] = df_weekly.index
    df_weekly["FinBalance_To_Cap_v2"] = df_weekly["MarginTrade_FinBalance"] / df_weekly["Total_Cap"]
    df_weekly["FinBalance_To_Cap_v2_Weekly_Diff"] = df_weekly["FinBalance_To_Cap_v2"].diff()
    # df.to_csv("d:/My_finbalance2Cap.csv")
    #
    print("融资余额 / 流通市值比 ver 2")
    print(df_weekly[["FinBalance_To_Cap_v2", "FinBalance_To_Cap_v2_Weekly_Diff"]])
    Save_Systematic_Factor_To_Database(database, df_weekly, 'FinBalance_To_Cap_v2')  #
    Save_Systematic_Factor_To_Database(database, df_weekly, 'FinBalance_To_Cap_v2_Weekly_Diff')  #


# ---融资融券---
def Calc_Margin_Trade(database, datetime1, datetime2):
    #
    # df_financingbalance_sh = EDB('M0061606', datetime1, datetime2)  # 融资余额 上海
    # df_financingbalance_sz = EDB('M0061610', datetime1, datetime2)  # 融资余额 深圳
    df_financingbalance = EDB('M0075990', datetime1, datetime2, 'MarginTrade_FinBalance')  # 融资余额
    df_creditbalance = EDB('M0075991', datetime1, datetime2, 'MarginTrade_ShortBalance')  # 融券余额
    #
    df_fin_buy = EDB('M0075987', datetime1, datetime2)  # 融资买入额（全市场）
    df_sh_fin_buy = EDB('M0061604', datetime1, datetime2, 'FinancingBuy_SH')  # 沪市 融资买入
    df_sh_fin_sel = EDB('M0061605', datetime1, datetime2, 'FinancingSel_SH')  # 沪市 融资偿还
    df_sz_fin_buy = EDB('M0061609', datetime1, datetime2, 'FinancingBuy_SZ')  # 深市 融资买入
    df_sz_fin_sel = EDB('M0075952', datetime1, datetime2, 'FinancingSel_SZ')  # 深市 融资偿还
    #
    df_sh_trade_amt = EDB('M0020199', datetime1, datetime2, "TotalAmt_SH")  # 上海 成交金额 上证A股指数
    df_sz_trade_amt = EDB('M0020276', datetime1, datetime2, "TotalAmt_SZ")  # 深圳 成交金额 深圳综合指数
    # df_sh_totalamt = EDB('G8324468', datetime1, datetime2, 'TotalAmt_SH')  # SH A股 总成交金额，港交所数据 # 单位百万元
    # df_sz_totalamt = EDB('G8324481', datetime1, datetime2, 'TotalAmt_SZ')  # SZ A股 总成交金额，港交所数据 # 单位百万元
    df_circulate_cap_sh = EDB('G8324465', datetime1, datetime2, 'CirculateCap_SH')  # 上海市值
    df_circulate_cap_sz = EDB('G8324478', datetime1, datetime2, 'CirculateCap_SZ')  # 深圳市值

    # ---基础因子---
    df = pd.merge(df_financingbalance, df_creditbalance, how="left", left_index=True, right_index=True)
    df["MarginTrade_FinShortGap"] = df["MarginTrade_FinBalance"] - df["MarginTrade_ShortBalance"]  # 融资融券差
    #
    # print(df)
    Save_Systematic_Factor_To_Database(database, df, 'MarginTrade_FinBalance')  # 融资余额
    Save_Systematic_Factor_To_Database(database, df, 'MarginTrade_ShortBalance')  # 融券余额
    Save_Systematic_Factor_To_Database(database, df, 'MarginTrade_FinShortGap')
    Save_Systematic_Factor_To_Database(database, df_fin_buy, 'MarginTrade_Financing_Buy', field_name="M0075987")

    # ---融资交易额 / 总交易额，占比---
    # ---计算 融资交易额（融资买入+融资偿还）* 0.5（单边计算）  / 总交易额 ---
    df = pd.concat([df_sh_trade_amt, df_sz_trade_amt,
                    df_sh_fin_buy, df_sh_fin_sel, df_sz_fin_buy, df_sz_fin_sel, df_fin_buy], axis=1, sort=True)
    df["Release_Date"] = df["Report_Date"] = df.index
    Calc_MarginTrade_FinAmt_2_TotalAmt(database, df, datetime1, datetime2)
    Calc_MarginTrade_FinAmt_2_TotalAmt_v2(database, df, datetime1, datetime2)

    # --- 融资额 市值占比---
    df = pd.concat([df_financingbalance, df_circulate_cap_sh, df_circulate_cap_sz], axis=1, sort=True)
    df["Release_Date"] = df["Report_Date"] = df.index
    Calc_MarginTrade_FinBalance_2_Cap(database, df, datetime1, datetime2)
    Calc_MarginTrade_FinBalance_2_Cap_v2(database, df, datetime1, datetime2)


def Calc_Volatility(database, datetime1, datetime2):
    df = EDB('M0331236', datetime1, datetime2, 'CBOE_VIX_50ETF')  # 波动率:50ETF期权（可能是隐含波动率）
    print(df)
    Save_Systematic_Factor_To_Database(database, df, 'CBOE_VIX_50ETF')


# ---换手率---
def Calc_Turnover(database, datetime1, datetime2):
    #
    # df_turn_sh = EDB('Turnover_SH', 'M0331264', datetime1, datetime2)
    df_total_cap_sh = EDB('G8324464', datetime1, datetime2, 'TotalCap_SH')  # 上证所:A股:总市值（港交所发布）
    df_amount_sh = EDB('G8324468', datetime1, datetime2, 'Amount_SH')  # 上证所:A股:总成交金额（港交所发布）
    #
    df_total_cap_sz = EDB('G8324477', datetime1, datetime2, 'TotalCap_SZ')  # 深交所:A股:总市值（港交所发布）
    df_amount_sz = EDB('G8324481', datetime1, datetime2, 'Amount_SZ')  # 深交所:A股:总成交金额（港交所发布）
    #
    df = pd.merge(df_total_cap_sh, df_amount_sh, how="left", left_index=True, right_index=True)
    df = pd.merge(df, df_total_cap_sz, how="left", left_index=True, right_index=True)
    df = pd.merge(df, df_amount_sz, how="left", left_index=True, right_index=True)

    # 百万转换为亿
    df["Amount_SH"] = df["Amount_SH"] * 0.01
    df["Amount_SZ"] = df["Amount_SZ"] * 0.01
    df["Turnover_SZ"] = df["Amount_SZ"] / df["TotalCap_SZ"]
    df["Turnover_SH"] = df["Amount_SH"] / df["TotalCap_SH"]
    df["Turnover_TotalA"] = (df["Amount_SH"] + df["Amount_SZ"]) / (df["TotalCap_SH"] + df["TotalCap_SZ"])

    print(df[["Turnover_SZ", "Turnover_SH", "Turnover_TotalA"]])
    Save_Systematic_Factor_To_Database(database, df, 'Turnover_SH')
    Save_Systematic_Factor_To_Database(database, df, 'Turnover_SZ')
    Save_Systematic_Factor_To_Database(database, df, 'Turnover_TotalA')
    #
    Save_Systematic_Factor_To_Database(database, df, save_name='Trading_Amount_SH', field_name="Amount_SH")
    Save_Systematic_Factor_To_Database(database, df, save_name='Trading_Amount_SZ', field_name="Amount_SZ")


# 计算换手率历史分位排名
def Calc_Turnover_Rank(database, datetime1, datetime2):
    #
    Calc_Turnover_Rank_Sub(database, "000300.SH", datetime.date(2005, 4, 1), alias_name="HS300")


def Calc_Turnover_Rank_Sub(database, symbol, start_date, alias_name=""):
    #
    if alias_name == "":
        alias_name = symbol
    #
    filter = [("Symbol", symbol), ("date", ">=", start_date)]
    df = database.GetDataFrame("financial_data", "Index_Dailybar", filter=filter, projection=["date", "volume", "free_float_shares", "free_turn"])
    df["test"] = df["volume"] / df["free_float_shares"]
    df["Report_Date"] = df["Release_Date"] = df["date"]
    # print(df)
    #
    df["turnover"] = df["test"].rolling(window=20, min_periods=1).mean()
    #
    df = fill_historical_probability_percentile(df, "turnover", return_raw_data=True)
    df.set_index("date", inplace=True)
    print(df)
    #
    df_weekly = df[['turnover_Rank', "Release_Date"]].resample('W').last()
    df_weekly['Report_Date'] = df_weekly.index
    #
    print(df_weekly)
    Save_Systematic_Factor_To_Database(database, df_weekly,
                                       save_name="Turnover_" + alias_name + "_Rank",
                                       field_name='turnover_Rank')


# 成交量相关因子
def Calc_AMT(database, datetime1, datetime2):
    Calc_AMT_HS300(database, datetime1, datetime2)


def Calc_AMT_HS300(database, datetime1, datetime2):
    #
    filter = [("Symbol", "000300.SH")]
    filter.append(("date", ">=", datetime1))
    filter.append(("date", "<=", datetime2))
    df = database.GetDataFrame("financial_data", "Index_dailybar", filter=filter, projection=["date", "money"])
    df["Release_Date"] = df["date"]
    df.set_index("date", inplace=True)
    #
    df_weekly = df[['money', "Release_Date"]].resample('W').agg({"money": "mean", "Release_Date": "last"})
    df_weekly["amt"] = np.log(df_weekly["money"])
    df_weekly["dif"] = df_weekly["amt"].diff(1)
    df_weekly['Report_Date'] = df_weekly.index
    print(df_weekly)
    Save_Systematic_Factor_To_Database(database, df_weekly, save_name='HS300_Amt_Weekly_Dif', field_name='dif')


def Calc_Weekly_Return(database, datetime1, datetime2):
    #
    df = Calc_Period_Return_Database(database, symbol="000300.SH", datetime1=datetime1, datetime2=datetime2, period="weekly", is_log=False)
    Save_Systematic_Factor_To_Database(database, df, save_name='HS300_Weekly_Return', field_name='return')
    #
    df = Calc_Period_Return_Database(database, symbol="H11001.CSI", datetime1=datetime1, datetime2=datetime2, period="weekly", is_log=False)
    Save_Systematic_Factor_To_Database(database, df, save_name='H11001_Weekly_Return', field_name='return')
    #
    df = Calc_Period_Return_Database(database, symbol="H11001.CSI", datetime1=datetime1, datetime2=datetime2, period="monthly", is_log=False)
    Save_Systematic_Factor_To_Database(database, df, save_name='H11001_Monthly_Return', field_name='return')
    #
    df = Calc_Period_Return_EDB("AUFI.WI", datetime1, datetime2, period="Weekly", is_log=True)  # 黄金
    Save_Systematic_Factor_To_Database(database, df, save_name='Au_Weekly_Return', field_name='return')

    df = Calc_Period_Return_EDB("B00.IPE", datetime1, datetime2, period="Weekly", is_log=True)  # 石油
    Save_Systematic_Factor_To_Database(database, df, save_name='Brent_Weekly_Return', field_name='return')
    #
    df = Calc_Period_Return_EDB("CCFI.WI", datetime1, datetime2, period="Weekly", is_log=True)  # 大宗商品
    Save_Systematic_Factor_To_Database(database, df, save_name='Wind_Commodity_Weekly_Return', field_name='return')
    #
    df = Calc_Period_Return_EDB("USDCNY.EX", datetime1, datetime2, period="Weekly", is_log=True)  # 美元人民币
    Save_Systematic_Factor_To_Database(database, df, save_name='USDCNY_Weekly_Return', field_name='return')
    # #
    df = Calc_Period_Return_EDB("891800.MI", datetime1, datetime2, period="Weekly", is_log=True)  # 新兴市场
    Save_Systematic_Factor_To_Database(database, df, save_name='MSCI_EM_Weekly_Return', field_name='return')
    #
    df = Calc_Period_Return_EDB("990100.MI", datetime1, datetime2, period="Weekly", is_log=True)  # 发达国家
    Save_Systematic_Factor_To_Database(database, df, save_name='MSCI_DM_Weekly_Return', field_name='return')


# 沪深300周收益率
def Calc_HS300_Weekly_Return(database, datetime1, datetime2):
    # filter = []
    # datetime0 = datetime1 + datetime.timedelta(days=-15)
    # filter.append(["symbol", "000300.SH"])
    # filter.append(["date", ">=", datetime0])
    # filter.append(["date", "<=", datetime2])
    # #
    # documents = database.Find("index", "dailybar", filter)
    # df = Gadget.DocumentsToDataFrame(documents, keep=["date","close"])
    # # print(df)
    #
    # # 数据预处理
    # df["date_dt"] = pd.to_datetime(df["date"])
    # df.set_index("date_dt", inplace=True)
    # df_weekly = df.resample("W").last()
    # # df_weekly = df.resample("W-Fri").last()
    #
    # # 计算收益
    # df_weekly["Return"] = df_weekly["close"] / df_weekly["close"].shift(1) - 1
    #
    # # 重新确定起始位置
    # df_weekly = df_weekly[(df_weekly["date"] >= datetime1.date()) & (df_weekly["date"] <= datetime2.date())]
    # # print(df_weekly)
    #
    # # 储存因子
    # df_weekly.dropna(subset=["Return"], inplace=True) # 不存储nan数据
    # df_weekly["Report_Date"] = df_weekly.index
    # df_weekly["Release_Date"] = df_weekly["date"]
    # Save_Systmetic_Factor_To_Database(database, df_weekly, savedName="HS300_Weekly_Return", fieldName="Return")

    df = Calc_Period_Return_Database(database, symbol="000300.SH", datetime1=datetime1, datetime2=datetime2,
                                     period="weekly", is_log=False)
    print(df)
    Save_Systematic_Factor_To_Database(database, df, save_name='HS300_Weekly_Return', field_name='Return')


# 中债收益率
def Calc_Bond_Index_Weekly_Return(database, datetime1, datetime2):
    #
    # df1 = Query_DailyBar(database, 'H11001.CSI', datetime1, datetime2)
    # df1['yearmonth'] = df1.index.map(lambda x :100*x.year+x.month)
    # df1 = df1.groupby('yearmonth').last()
    # df1 = np.log(df1)
    # df1['factor'] = df1.diff()
    # df1['Report_Date'] = df1.index.astype(str).map(lambda x: x[0:4] + '-' + x[4:]).map(lambda x: pd.datetime.strptime(x, '%Y-%m')).map(lambda x: x + relativedelta(months=+1))
    # df1['Release_Date'] = df1['Report_Date']
    # print(df1)

    df = Calc_Period_Return_Database(database, symbol="H11001.CSI", datetime1=datetime1, datetime2=datetime2,
                                     period="monthly", is_log=False)
    df = df[:-1]
    print(df)
    Save_Systematic_Factor_To_Database(database, df, save_name='H11001_Monthly_Return', field_name='Return')


# Bondleverage
# 杠杆率 = 总资产/净资产
# 中债杠杆率 = 托管量/(托管量-待购回债券余额)
# 杠杆率 = 中债和上清托管量/(中债和上清托管量-正/逆回购余额总量)
def Calc_Bond_Leverage(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0041746',datetime1,datetime2)  # 债券托管量:本币债
    df2 = Query_Data(database, 'M0041747',datetime1,datetime2)  # 债券托管量:本币债:可流通债券
    df3 = Query_Data(database, 'M0041753',datetime1,datetime2)  # 待购回债券余额
    df33 = pd.concat([df1,df2,df3], axis=1)
    # df33['Report_Date'] = df33.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df33['Report_Date'] = df33.index
    Fill_ReleaseDate(df33, lag_release_month=1, release_day=8)
    df33['M0041746'] = df33['M0041746'].where(df33['M0041746'].notnull(), 1.05 * df33['M0041747'])
    df33['factor'] = df33['M0041746'] / (df33['M0041746'] - df33['M0041753'])
    print(df33)
    #return(df33,'Bond_Market_Leverage')
    Save_Systematic_Factor_To_Database(database, df33, save_name='Bond_Market_Leverage', field_name='factor')


# mmleverage
def Calc_MoneyMarket_leverage(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0041739',datetime1,datetime2)  # 成交量:银行间质押式回购
    df1.index = pd.to_datetime(df1.index)
    df1 = df1.resample("M").mean()
    #
    df2 = Query_Data(database, 'M0010096',datetime1,datetime2)  # 超额存款准备金率(超储率):金融机构
    df3 = Query_Data(database, 'M0009940',datetime1,datetime2)  # 金融机构:各项存款余额
    df4 = Query_Data(database, 'M0001386',datetime1,datetime2)  # 现金净投放:当月值
    df5 = pd.concat([df2,df3,df4],axis=1, sort=True)
    df5.sort_index(inplace=True)
    df5.fillna(method='ffill',inplace = True)#用前面的值来填充
    # print(df5)
    df5.index = pd.to_datetime(df5.index)
    df5 = df5.resample("M").last()
    #
    df34 = pd.merge(df1, df5, how="inner", left_index=True, right_index=True)
    df34['factor'] = (df34['M0041739']+df34['M0010096']/100*df34['M0009940']-df34['M0001386'])/(df34['M0010096']/100*df34['M0009940']-df34['M0001386'])
    df34['Report_Date'] = df34.index
    Fill_ReleaseDate(df34, lag_release_month=1, release_day=15)
    print(df34[["Report_Date", "Release_Date", "factor"]])
    # return(df34,'Money_Market_Leverage')
    df34.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df34, save_name='Money_Market_Leverage', field_name='factor')


# DIFLN_MKT_STKBD
def Calc_Stock_Cap_To_Bond(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0041746',datetime1,datetime2)  # 债券托管量:本币债
    df2 = Query_Data(database, 'M0041747',datetime1,datetime2)  # 债券托管量:本币债:可流通债券
    df3 = Query_Data(database, 'M0010339',datetime1,datetime2)  # 上证所:股票总市值
    df4 = Query_Data(database, 'M0010359',datetime1,datetime2)  # 深交所:股票总市值
    df37 = pd.concat([df1, df2, df3, df4], axis=1, sort=True)
    df37['Report_Date'] = df37.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    Fill_ReleaseDate(df37, lag_release_month=1, release_day=8)
    df37['M0041746'] = df37['M0041746'].where(df37['M0041746'].notnull(), 1.05 * df37['M0041747'])  #05年之前拼接
    df37['sum'] = df37['M0010339'] + df37['M0010359']
    df37['div'] = df37['M0041746'] / df37['sum']
    df37['log'] = np.log(df37['div'])
    df37['factor'] = df37['log'].diff()
    print(df37)

    # return(df37,'Stock_Cap_To_Bond_Dif')
    df37.dropna(subset=["div"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df37, save_name='Stock_Cap_To_Bond', field_name='div')
    df37.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df37, save_name='Stock_Cap_To_Bond_Monthly_Dif', field_name='factor')


# bond_stock
def Calc_Bond_Stock_Rotate(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0041742', datetime1, datetime2)  # 成交量:银行间同业拆借
    df2 = Query_Data(database, 'M0041741', datetime1, datetime2)  # 成交量:银行间买断式回购
    df3 = Query_Data(database, 'M0041740', datetime1, datetime2)  # 成交量:银行间债券现券  # 2021-2-26 中国货币网不再公布
    df4 = Query_Data(database, 'M0041739', datetime1, datetime2)  # 成交量:银行间质押式回购
    df5 = Query_Data(database, 'M0020192', datetime1, datetime2)  # 成交金额:上证综合指数
    df6 = Query_Data(database, 'M0020255', datetime1, datetime2)  # 成交金额:深证成份指数
    df35 = pd.concat([df1, df2, df3, df4, df5, df6], axis=1, sort=False)
    df35.index = pd.to_datetime(df35.index)
    df35["Release_Date"] = df35.index
    #
    df_date = df35.resample("M").last()
    df_date = df_date[["Release_Date"]]
    #
    df = df35.resample("M").mean()
    df = pd.merge(df, df_date, how="left", left_index=True, right_index=True)
    #
    df['sum'] = df['M0041742'] + df['M0041741'] + df['M0041740'] + df['M0041739']
    df['sum2'] = df['M0020192'] + df['M0020255']
    df['factor'] = df['sum'] / df['sum2']
    df['Report_Date'] = df.index
    print(df[["Release_Date", "Report_Date", "sum", "factor"]])
    df = df[:-1]
    # return(df35,'Bond_Stock_Rotate')
    Save_Systematic_Factor_To_Database(database, df, save_name='Bond_Stock_Rotate', field_name='factor')


# Bond_Future
def Calc_Bond_Future_Rotate(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0041742', datetime1, datetime2)  # 成交量:银行间同业拆借
    df2 = Query_Data(database, 'M0041741', datetime1, datetime2)  # 成交量:银行间买断式回购
    df3 = Query_Data(database, 'M0041740', datetime1, datetime2)  # 成交量:银行间债券现券
    df4 = Query_Data(database, 'M0041739', datetime1, datetime2)  # 成交量:银行间质押式回购
    df5 = Query_Data(database, 'M0024559', datetime1, datetime2)  # 成交金额:全国:期货合计:当月值

    df_bond = pd.concat([df1, df2, df3, df4], axis=1, sort=False)
    df_bond.index = pd.to_datetime(df_bond.index)
    df_bond["sum"] = np.sum(df_bond, axis=1)
    df_bond["Release_Date"] = df_bond.index
    #
    df_monthly = df_bond.resample("M").agg({"sum":"sum", "Release_Date":"last"})
    df_monthly["Report_Date"] = df_monthly.index
    #
    df = pd.merge(df_monthly, df5, how="inner", left_index=True, right_index=True)
    df['factor'] = df['sum'] / df['M0024559']
    df['Report_Date'] = df.index
    print(df[["Release_Date", "Report_Date", "sum", "factor"]])
    # return(df36,'Bond_Commodity_Rotate')

    Save_Systematic_Factor_To_Database(database, df, save_name='Bond_Commodity_Rotate', field_name='factor')


def Calc_Industries_Rotation(database, datetime1, datetime2):
    #
    industry_symbols = ["801010.SI", "801020.SI", "801030.SI", "801040.SI", "801050.SI", "801080.SI", "801110.SI", "801120.SI",
                        "801130.SI", "801140.SI", "801150.SI", "801160.SI", "801170.SI", "801180.SI", "801200.SI", "801210.SI",
                        "801230.SI", "801710.SI", "801720.SI", "801730.SI", "801740.SI", "801750.SI", "801760.SI", "801770.SI",
                        "801780.SI", "801790.SI", "801880.SI", "801890.SI"]

    # 周度Return
    industries_rotation = pd.DataFrame()
    for symbol in industry_symbols:
        #
        datai = WSD_Multi_Fields(symbol, datetime1, datetime2, fields=['pct_chg'], param="Period=W")
        # datai = IFind_History_Quote_MultiFields(symbol, datetime1, datetime2, fields=["changeRatio"], param="Interval:W")
        datai.columns = [symbol]
        industries_rotation = pd.concat([industries_rotation, datai], axis=1)

    # print(Industries_Rotation)
    industries_rotation.index = pd.to_datetime(industries_rotation.index)
    df_sd = industries_rotation.std(axis=1)
    df_sd = pd.DataFrame(df_sd)
    df_sd.columns = ['sd']
    df_sd["Release_Date"] = df_sd.index
    # print(df_sd)

    df_sd_weekly = df_sd.resample('W').last()
    df_sd_weekly['Report_Date'] = df_sd_weekly.index
    # print(df_sd_weekly)
    Save_Systematic_Factor_To_Database(database, df_sd_weekly, save_name='Industries_Return_Divergence_Weekly',
                                       field_name='sd')


# 20220208 添加MAS因子
def download_equity_index_valuation_factor(database, datetime1, datetime2):
    #
    def download_equity_valuation_factor_sub(database, datetime1, datetime2, symbol, alias):
        #
        df_valuation = WSD_Multi_Fields(symbol, datetime1, datetime2, fields=['pe_ttm', "pb_lf", "dividendyield2"])
        #
        Save_Systematic_Factor_To_Database(database, df_valuation, save_name='PE_TTM_' + alias, field_name="pe_ttm")
        Save_Systematic_Factor_To_Database(database, df_valuation, save_name='PB_LF_' + alias, field_name="pb_lf")
        Save_Systematic_Factor_To_Database(database, df_valuation, save_name='Dividend_Yield_12M_' + alias, field_name="dividendyield2")

    #
    download_equity_valuation_factor_sub(database, datetime1, datetime2, symbol="000300.SH", alias="HS300")
    download_equity_valuation_factor_sub(database, datetime1, datetime2, symbol="000016.SH", alias="SH50")
    download_equity_valuation_factor_sub(database, datetime1, datetime2, symbol="000905.SH", alias="ZZ500")
    download_equity_valuation_factor_sub(database, datetime1, datetime2, symbol="000852.SH", alias="ZZ1000")


def download_option_implied_volatility(database, datetime1, datetime2):
    #
    df_opt = WSD_Multi_Fields("510050.SH", datetime1, datetime2, fields=['iv_1m800', "iv_1m1000"])
    #
    Save_Systematic_Factor_To_Database(database, df_opt, save_name="OPT_1M_80_Strike_IV_50ETF", field_name="iv_1m800")
    Save_Systematic_Factor_To_Database(database, df_opt, save_name="OPT_1M_100_Strike_IV_50ETF", field_name="iv_1m1000")


if __name__ == '__main__':
    #
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    #
    datetime1 = datetime.datetime(2000, 1, 1)
    datetime2 = datetime.datetime(2020, 12, 31)
    w.start()

    #
    # Calc_Basis(database, datetime1, datetime2)
    # Calc_UpDown(database, datetime1, datetime2)
    # Calc_HS300_Weekly_Return(database, datetime1, datetime2)
    # Calc_Margin_Trade(database, datetime1, datetime2)
    # Calc_Turnover_Rank(database, datetime1, datetime2)
    # Calc_AMT_HS300(database, datetime1, datetime2)
    # Calc_Period_Return_EDB(database, "AUFI.WI", datetime1, datetime2)
    # Calc_Weekly_Return(database, datetime1, datetime2)

    datetime1 = datetime.datetime(2024, 6, 21)
    datetime2 = datetime.datetime(2024, 9, 30)
    # Calc_Turnover(database, datetime1, datetime2)  # 市场情绪-换手率


    datetime1 = datetime.datetime(2020, 11, 1)
    datetime2 = datetime.datetime(2020, 12, 31)
    # Calc_Industries_Rotation(database, datetime1, datetime2)

    datetime1 = datetime.datetime(2024, 11, 1)
    datetime2 = datetime.datetime(2020, 12, 31)
    Calc_Bond_Stock_Rotate(database, datetime1, datetime2)

    datetime1 = datetime.datetime(2023, 11, 1)
    datetime2 = datetime.datetime(2024, 4, 26)
    # download_equity_index_valuation_factor(database, datetime1, datetime2)
    # download_option_implied_volatility(database, datetime1, datetime2)

    # df = Calc_Period_Return_Database(database, symbol="H11001.CSI", datetime1=datetime1, datetime2=datetime2, period="weekly", is_log=False)
    # Save_Systmetic_Factor_To_Database(database, df, save_name='H11001_Weekly_Return', field_name='return')